﻿using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using Excel;
using System.Web;
using System.Web.UI.HtmlControls;
using System.Web.Security;

public partial class Customers : System.Web.UI.Page
{
    DataSet ds = new DataSet();
    public static string loggedInUserName = "";
    public static string loggedInUserType = "";

    static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["LoginName"] == null || Session["UserId"] == null)
        {
            Response.Redirect("Home.aspx");
        }
        else
        {
            if (Session["UserTypeId"] != null && Convert.ToInt32(Session["UserTypeId"]) == 1)
            {
                if (!IsPostBack)
                {
                    loggedInUserType = Session["UserType"].ToString();
                    loggedInUserName = Session["LoginName"].ToString();
                    BindCustomers();
                }
            }
            else
            {
                FormsAuthentication.SignOut();
                Session["LoginName"] = "";
                Session["UserType"] = "";
                Session["MobileNumber"] = "";
                Session["EmailId"] = "";
                Response.Redirect("Home.aspx");
            }

        }
    }


    public void BindCustomers()
    {
        DataTable dt = EventDAO.getCustomers();
        ViewState["dt"] = dt;
        Grid.DataSource = dt;
        Grid.DataBind();
    }

    protected void UploadBtn_Click(object sender, EventArgs e)
    {
        if (FileUpload1.PostedFile != null)
        {
            string path = Path.GetFileName(FileUpload1.PostedFile.FileName);
            if (!String.IsNullOrEmpty(path))
            {
                string filePath = Server.MapPath("~/docs//") + path;
                string ext = Path.GetExtension(filePath);
                FileUpload1.SaveAs(filePath);

                //http://exceldatareader.codeplex.com/
                FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = null;
                DataSet result = null;
                try
                {
                    if (ext.Equals(".xlsx"))
                    {
                        excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);   //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                        excelReader.IsFirstRowAsColumnNames = true;
                        result = excelReader.AsDataSet();
                    }
                    else if (ext.Equals(".xls"))
                    {
                        excelReader = ExcelReaderFactory.CreateBinaryReader(stream);      //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                        excelReader.IsFirstRowAsColumnNames = true;
                        result = excelReader.AsDataSet();
                    }
                    else
                        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "key_name", "alert('Invalid file!!!   Please upload microsoft excel sheet in the 97-2007 format');", true);
                }
                catch (Exception ex)
                {
                    ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "key_name", "alert('Invalid file!!!   Please upload microsoft excel sheet in the 97-2007 format');", true);
                }
                if (excelReader == null)
                {
                    ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "key_name", "alert('Invalid file!!!   Please upload microsoft excel sheet in the 97-2007 format');", true);
                }
                else
                {
                    //http://www.eggheadcafe.com/community/csharp/2/10337724/datatable-to-mysql.aspx
                    if (result != null && result.Tables.Count > 0)
                    {
                        DataTable dt = result.Tables[0];

                        foreach (DataRow row in dt.Rows)
                        {
                            Customer customer = new Customer();
                            customer.name = row["name"] != null ? row["name"].ToString() : "";
                            customer.address = row["address"] != null ? row["address"].ToString() : "";
                            customer.email = row["email"] != null ? row["email"].ToString() : "";
                            customer.contact_person = row["contact_person"] != null ? row["contact_person"].ToString() : "";
                            customer.contact_number = row["contact_number"] != null ? row["contact_number"].ToString() : "";

                            if (!String.IsNullOrEmpty(customer.name))
                            {
                                string status = EventDAO.insertCustomer(customer);
                            }
                            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "key_name", "alert('Customer data updated.');", true);
                        }
                    }
                }
                excelReader.Close();
            }
        }
        else
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "key_name", "alert('Invalid file!!!   Please select a microsoft excel sheet in the 97-2007 format');", true);
        }

        Response.Redirect("Customers.aspx");
    }

    #region Group
    protected void Grid_EditCommand(object source, DataGridCommandEventArgs e)
    {
        Grid.EditItemIndex = e.Item.ItemIndex;
        BindCustomers();
    }
    protected void Grid_CancelCommand(object source, DataGridCommandEventArgs e)
    {
        Grid.EditItemIndex = -1;
        BindCustomers();
    }
    protected void Grid_DeleteCommand(object source, DataGridCommandEventArgs e)
    {
        int custid = Convert.ToInt32(Grid.DataKeys[(int)e.Item.ItemIndex]);
        EventDAO.deleteCustomer(custid);
        Grid.EditItemIndex = -1;
        BindCustomers();
    }
    protected void Grid_UpdateCommand(object source, DataGridCommandEventArgs e)
    {
        Customer c = new Customer();
        
        int custId = Convert.ToInt32(Grid.DataKeys[(int)e.Item.ItemIndex]);
        c.name = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
        c.address = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
        c.email = ((TextBox)e.Item.Cells[4].Controls[0]).Text;
        c.contact_person = ((TextBox)e.Item.Cells[5].Controls[0]).Text;
        c.contact_number = ((TextBox)e.Item.Cells[6].Controls[0]).Text;
        int val = EventDAO.updateCustomer(c,custId);

        Grid.EditItemIndex = -1;
        BindCustomers();
    }
    #endregion
}
